﻿using System;
using System.Collections.Generic;
using System.Linq;
using SGQ.Model;
using System.Data.SqlServerCe;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Windows.Forms;

namespace SGQ.Controller
{
    class ControllerMaterial
    {
        public ControllerMaterial() { 
            conn = new ConnectFactory();
        }

        private ConnectFactory conn;
        public const string strDelete = "Delete Material where Codigo = (@Codigo)";
        public const string strInsert = "Insert into Material (Codigo_Departamento,Data,Titulo,Conteudo,Codigo_Usuario) values (@Codigo_Departamento,@Data,@Titulo,@Conteudo,@Codigo_Usuario)";
        public const string strSelect = "Select * From Material order by Codigo_Departamento";
        public const string strUpdate = "Update Material set Codigo_Departamento = (@Codigo_Departamento), Data = (@Data), Titulo = (@Titulo), Conteudo = (@Conteudo), Codigo_Usuario = (@Codigo_Usuario) Where Codigo = (@Codigo)";
        private ConnectFactory objConexao;

        public bool Insert(Material mat) 
        {
            try
            {
                using (objConexao = new ConnectFactory())
                {
                    using (SqlCeCommand objCommand = new SqlCeCommand(strInsert, objConexao.CreateConnection()))
                    {
                        objCommand.Parameters.AddWithValue("@Codigo_Departamento", mat.Departamento.Codigo);
                        objCommand.Parameters.AddWithValue("@Data", mat.Data);
                        objCommand.Parameters.AddWithValue("@Titulo", mat.Titulo);
                        objCommand.Parameters.AddWithValue("@Conteudo", mat.Conteudo);
                        objCommand.Parameters.AddWithValue("@Codigo_Usuario", mat.Usuario.Codigo);
                        int lines = objCommand.ExecuteNonQuery();
                        if (lines > 0)
                        {
                            return true;
                        }
                        else {
                            return false;
                        }
                    }
                }
            }catch (Exception e)
            {
                throw new Exception(e.Message,e);
            }
        }

		public bool Update(Material mat) 
        {
            try
            {
                using (objConexao = new ConnectFactory())
                {
                    using (SqlCeCommand objCommand = new SqlCeCommand(strUpdate, objConexao.CreateConnection()))
                    {
                        objCommand.Parameters.AddWithValue("@Codigo_Departamento", mat.Departamento.Codigo);
                        objCommand.Parameters.AddWithValue("@Data", mat.Data);
                        objCommand.Parameters.AddWithValue("@Titulo", mat.Titulo);
                        objCommand.Parameters.AddWithValue("@Conteudo", mat.Conteudo);
                        objCommand.Parameters.AddWithValue("@Codigo_Usuario", mat.Usuario.Codigo);

						objCommand.Parameters.AddWithValue("@Codigo", mat.Codigo);
                        int lines = objCommand.ExecuteNonQuery();
                        if (lines > 0)
                        {
                            return true;
                        }
                        else {
                            return false;
                        }
                    }
                }
            }catch (Exception e)
            {
                throw new Exception(e.Message,e);
            }
        }

		public bool Delete(Material mat) 
        {
            try
            {
                using (objConexao = new ConnectFactory())
                {
                    using (SqlCeCommand objCommand = new SqlCeCommand(strDelete, objConexao.CreateConnection()))
                    {
						objCommand.Parameters.AddWithValue("@Codigo", mat.Codigo);
                        int lines = objCommand.ExecuteNonQuery();
                        if (lines > 0)
                        {
                            return true;
                        }
                        else {
                            return false;
                        }
                    }
                }
            }catch (Exception e)
            {
                throw new Exception(e.Message,e);
            }
        }

		public DataSet ListDataSet() 
        {
            try
            {
                using (objConexao = new ConnectFactory())
                {
                    using (SqlCeCommand objCommand = new SqlCeCommand(strSelect, objConexao.CreateConnection()))
                    {
                        SqlCeDataAdapter adapter = new SqlCeDataAdapter(objCommand);
						DataSet dataSet = new DataSet();
						adapter.Fill(dataSet);
						return (dataSet);   
                    }
                }
            }catch (Exception e)
            {
                throw new Exception(e.Message,e);
            }
        }

    }
}
